﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;

namespace CS2102_Music.Logic
{
    public class QueryResult
    {
        public List<string> Data { get; set; }
        public int ColumnCount { get; private set; }
        public int RowCount
        {
            get
            {
                return Data.Count / ColumnCount;
            }
        }

        public QueryResult(int columnCount, int estimatedSize = 0)
        {
            ColumnCount = columnCount;
            if (estimatedSize > 0)
            {
                Data = new List<string>(estimatedSize);
            }
            else
            {
                Data = new List<string>();
            }
        }
        public string At(int row, int column)
        {
            return Data[(row * ColumnCount) + column];
        }
        public int GetInt(int row, int column)
        {
            return Convert.ToInt32(Data[(row * ColumnCount) + column]);
        }
        public DateTime GetDateTime(int row, int column)
        {
            return Convert.ToDateTime(Data[(row * ColumnCount) + column]);
        }
        public decimal GetDecimal(int row, int column)
        {
            return Convert.ToDecimal(Data[(row * ColumnCount) + column]);
        }
        public Double GetDouble(int row, int column)
        {
            return Convert.ToDouble(Data[(row * ColumnCount) + column]);
        }
        public char GetChar(int row, int column)
        {
            return Convert.ToChar(Data[(row * ColumnCount) + column]);
        }
    }
    public class QueryParameter
    {
        public string Name { get; set; }
        public string Value { get; set; }
        public QueryParameter(string name, string value)
        {
            Name = name;
            Value = value;
        }
    }
    public class DbConnector
    {
        //properties
        public string Server { get; set; }
        public string Database { get; set; }
        public string Userid { get; set; }
        public string Password { get; set; }

        public string Error
        {
            get;
            private set;
        }

        private bool Connected = false;
        private bool TransactionStarted = false;
        private MySqlConnection Connection;
        private MySqlTransaction Transaction = null;


        public DbConnector()
        {
            Server = "localhost";
            Database = "musicstore";
            Userid = "root";
            Password = "password";
        }
        ~DbConnector()
        {
            if (TransactionStarted)
            {
                Transaction.Rollback();
            }
            if (Connected)
            {
                Connection.Close();
            }
        }
        public bool Connect()
        {
            if (!Connected)
            {
                string connectionString = Configuration.DatabaseConnectionString;

                Connection = new MySqlConnection(connectionString);
                try
                {
                    Connection.Open();
                    Connected = true;
                }
                catch (MySqlException ex)
                {
                    switch (ex.Number)
                    {
                        case 0:
                            Error = "Cannot connect to server, Contact your administrator";
                            break;
                        case 1:
                            Error = "invalid username/password";
                            break;
                        default:
                            Error = "unidentified error";
                            break;

                    }
                    return false;
                }
                //ExecuteNonQuery("SET sql_mode = ALLOW_INVALID_DATES");
            }
            return true;
        }
        public void Disconnect()
        {
            if (Connected)
            {
                Connected = false;
                Connection.Close();
            }
        }

        public void StartTransaction()
        {
            if (!TransactionStarted)
            {
                TransactionStarted = true;
                Transaction = Connection.BeginTransaction();
            }
        }
        public void CommitTransaction()
        {
            if (TransactionStarted)
            {
                Transaction.Commit();
                Transaction = null;
                TransactionStarted = false;
            }
        }
        public void RollbackTransaction()
        {
            if (TransactionStarted)
            {
                Transaction.Rollback();
                Transaction = null;
                TransactionStarted = false;
            }
        }

        //ExecuteQuery and ExecuteReaderQuery does the same thing, just that ExecuteReaderQuery returns a DataReader
        //Use ExecuteQuery if you are unsure how to use the MySqlDataReader

        //ExecuteQuery
        //Executes a "select statement"
        //returns QueryResult, which will holds the tuples of the query result.
        //use QueryResult.At to retrieve the data of each tuple and column
        //parameters: query         - the query that is to be executed
        public QueryResult ExecuteQuery(string query, params QueryParameter[] queryParams)
        {
            bool isConnected = Connect();
            if (!isConnected)
            {
                return null;
            }
            MySqlCommand command = new MySqlCommand(query, Connection);
            foreach (var param in queryParams)
            {
                command.Parameters.AddWithValue(param.Name, param.Value);
            }
            MySqlDataReader reader = command.ExecuteReader();
            QueryResult qResult = new QueryResult(reader.FieldCount);
            while (reader.Read())
            {
                for (int i = 0; i < qResult.ColumnCount; i++)
                {
                    if (!reader.IsDBNull(i))
                    {
                        qResult.Data.Add(reader.GetString(i));
                    }
                    else
                    {
                        qResult.Data.Add(null);
                    }

                }
            }
            reader.Close();
            return qResult;
        }
        public QueryResult ExecuteQuery(string query, List<QueryParameter> queryParams)
        {
            QueryParameter[] paras = new QueryParameter[queryParams.Count];
            for (int i = 0; i < queryParams.Count; i++)
            {
                paras[i] = queryParams.ElementAt(i);
            }
            return ExecuteQuery(query, paras);
        }
        public MySqlDataReader ExecuteReaderQuery(string query, params QueryParameter[] queryParams)
        {
            bool isConnected = Connect();
            if (!isConnected)
            {
                return null;
            }
            MySqlCommand command = new MySqlCommand(query, Connection);
            foreach (var param in queryParams)
            {
                command.Parameters.AddWithValue(param.Name, param.Value);
            }
            MySqlDataReader reader = command.ExecuteReader();
            return reader;
        }
        public MySqlDataReader ExecuteReaderQuery(string query, List<QueryParameter> queryParams)
        {
            QueryParameter[] paras = new QueryParameter[queryParams.Count];
            for (int i = 0; i < queryParams.Count; i++)
            {
                paras[i] = queryParams.ElementAt(i);
            }
            return ExecuteReaderQuery(query, paras);
        }

        //execute queries that does not return any data, eg. Update, Delete
        public bool ExecuteNonQuery(string statement, params QueryParameter[] queryParams)
        {
            bool isConnected = Connect();
            if (!isConnected)
            {
                return false;
            }
            MySqlCommand command = new MySqlCommand(statement, Connection);
            foreach (var param in queryParams)
            {
                command.Parameters.AddWithValue(param.Name, param.Value);
            }
            command.ExecuteNonQuery();
            return true;
        }
        public bool ExecuteNonQuery(string query, List<QueryParameter> queryParams)
        {
            QueryParameter[] paras = new QueryParameter[queryParams.Count];
            for (int i = 0; i < queryParams.Count; i++)
            {
                paras[i] = queryParams.ElementAt(i);
            }
            return ExecuteNonQuery(query, paras);
        }

    }
}